
#region Imported Namespaces
using System;
using System.Runtime.InteropServices;
using OAB = PHSRAG.OfficeAutomationBridge;
using XL = Microsoft.Office.Interop.Excel;
using System.Threading;
using System.Collections;
#endregion

namespace PHSRAG.Insight21.Formatters
{
	#region Class ExcelFormatter
	public class ExcelFormatter : OAB.IExcelFormatter
	{
		#region IExcelFormatter Members
		/// <summary>
		/// Method invoked by the OfficeAutomationBridge within the context of invoking its Format() method.
		/// </summary>
		/// <param name="workBook">Reference to an open Excel workbook to format</param>
		/// <param name="formatInfo">Additional formatting instructions. For now, it will
		/// only have the ExcelFormatterReport enumerated type passed from the page.</param>
		/// <remarks>NOTE: assumes fixed addresses for filter criteria cells.</remarks>
		public void Format(XL.Workbook workBook, params object[] formatInfo)
		{
			XL.Sheets sheets = null;
			XL.Worksheet workSheet = null;
			XL.Range range = null;
			XL.Range criteriaRow = null;
			XL.Range lastCell = null;
			XL.Range lastRow = null;
			XL.Range headerCell = null;
			XL.Range headerRow = null;
			XL.Font font = null;

			Thread currentThread = Thread.CurrentThread;
			currentThread.Priority = ThreadPriority.BelowNormal;

			try 
			{
				sheets = workBook.Sheets;
				workSheet = (XL.Worksheet)sheets.get_Item(1); // all reports have one worksheet
				range = workSheet.Columns;

				//NOTE: assumes fixed addresses for filter criteria cells;
				const string filterCriteriaRowStartAddress	= "B3";
				const string filterCriteriaRowEndAddress	= "Z3";

                // only merge these cells if this is not the ClinicalTrialsVisits report, as it has a different header format
                if (formatInfo.Length == 3 && formatInfo[0] != null && (string)formatInfo[0] != "ClinicalTrialsVisits")
                {
                    // get the cells with the filter criteria and merge the cells (as the row could get really long)
                    criteriaRow = range.get_Range(filterCriteriaRowStartAddress, filterCriteriaRowEndAddress);
                    criteriaRow.Merge(Type.Missing);
                }
			
				// get the last row (with the footer) and merge all cells before autofitting
				lastCell = range.SpecialCells(XL.XlCellType.xlCellTypeLastCell, Type.Missing);

				string headerRowAddress = "A4";
				
				if (formatInfo.Length == 3 && formatInfo[0] != null)
				{
					switch ((string)formatInfo[0])
					{
						case "FinancialSummary" :
						case "AgreementSummary" :
							FormatFinancialSummary(range, lastCell);
							break;
						case "ProjectionsDetails" :
							headerRowAddress = "A13";
							FormatProjectionsDetails(range, lastCell, (int)formatInfo[2]);
							break;
						case "Budget" :
							FormatBudget(range, lastCell);
							break;
						case "Expense" :
							headerRowAddress = "A5";
							FormatExpense(range, lastCell, (int)formatInfo[1]);
							break;
						case "PersonnelDetails" :
							FormatPersonnelDetails(range, lastCell);
							break;
                        case "ClinicalTrialsVisits" :
                            break;
						default:
							throw new Exception("Unsupported ExcelFormatterReport.");
					}
				}

				// bold header
				headerCell = range.get_Range(headerRowAddress, headerRowAddress);
				headerRow = headerCell.EntireRow;
				font = headerRow.Font;
				font.Bold = true;

				lastRow = lastCell.EntireRow;
				lastRow.Merge(Type.Missing);
				range.AutoFit();

				workBook.Save();
			} 
			finally 
			{	
				if (lastRow != null)
					Marshal.ReleaseComObject(lastRow);
				if (font != null)
					Marshal.ReleaseComObject(font);
				if (headerRow != null)
					Marshal.ReleaseComObject(headerRow);
				if (headerCell != null)
					Marshal.ReleaseComObject(headerCell);
				if (lastCell != null)
					Marshal.ReleaseComObject(lastCell);
				if (criteriaRow != null)
					Marshal.ReleaseComObject(criteriaRow);
				if (range != null)
					Marshal.ReleaseComObject(range);
				if (workSheet != null)
					Marshal.ReleaseComObject(workSheet);
				if (sheets != null)	
					Marshal.ReleaseComObject(sheets);
			}
		}
		#endregion

		#region Private Formatting Methods
		/// <summary>
		/// Adds formula to financial summary excel report.
		/// </summary>
		/// <param name="range">All columns within a workbook.</param>
		/// <param name="lastCell">The last cell with data.</param>
		private void FormatFinancialSummary(XL.Range range, XL.Range lastCell) 
		{
			XL.Range formulaCell = null;

			#region begin column totals
			string lastCellAdress = lastCell.get_Address(false, false, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
			int lastCellRowNumber = Convert.ToInt32(lastCellAdress.Substring(1)); // last cell address is Z..

			// total budget
			string totalCellAddress = string.Format("E{0}",lastCellRowNumber - 2);
			string totalFormulaLastCellAddress = string.Format("E{0}", lastCellRowNumber - 3);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(E5:{0})",totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);

			// last mo expenses
			totalCellAddress = string.Format("F{0}",lastCellRowNumber - 2);
			totalFormulaLastCellAddress = string.Format("F{0}", lastCellRowNumber - 3);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(F5:{0})",totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);

			// expenses to date
			totalCellAddress = string.Format("G{0}",lastCellRowNumber - 2);
			totalFormulaLastCellAddress = string.Format("G{0}", lastCellRowNumber - 3);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(G5:{0})",totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);

			// budget balance
			totalCellAddress = string.Format("H{0}",lastCellRowNumber - 2);
			totalFormulaLastCellAddress = string.Format("H{0}", lastCellRowNumber - 3);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(H5:{0})",totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);
				
			// total revenue
			totalCellAddress = string.Format("I{0}",lastCellRowNumber - 2);
			totalFormulaLastCellAddress = string.Format("I{0}", lastCellRowNumber - 3);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(I5:{0})",totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);

			// current cash balance
			totalCellAddress = string.Format("J{0}",lastCellRowNumber - 2);
			totalFormulaLastCellAddress = string.Format("J{0}", lastCellRowNumber - 3);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(J5:{0})",totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);

			// open po/ap balance
			totalCellAddress = string.Format("K{0}",lastCellRowNumber - 2);
			totalFormulaLastCellAddress = string.Format("K{0}", lastCellRowNumber - 3);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(K5:{0})",totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);

			// planned salary
			totalCellAddress = string.Format("L{0}",lastCellRowNumber - 2);
			totalFormulaLastCellAddress = string.Format("L{0}", lastCellRowNumber - 3);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(L5:{0})",totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);
				
			// projected balance
			totalCellAddress = string.Format("M{0}",lastCellRowNumber - 2);
			totalFormulaLastCellAddress = string.Format("M{0}", lastCellRowNumber - 3);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(M5:{0})",totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);

			#endregion

			#region begin other formula within rows
			int firstDataRowNumber = 5;
			int	lastDataRowNumber = lastCellRowNumber - 3;
			string agreementType = null;
			string formulaCellAddress;
			for (int rowNumber = firstDataRowNumber; rowNumber < lastDataRowNumber + 1; ++rowNumber)
			{
				// budget balance
				formulaCellAddress = string.Format("H{0}", rowNumber);					
				formulaCell = range.get_Range(formulaCellAddress, formulaCellAddress);
				formulaCell.Formula = string.Format("=E{0}-G{0}", rowNumber);
				Marshal.ReleaseComObject(formulaCell);

				// current cach balance
				formulaCellAddress = string.Format("J{0}", rowNumber);
				formulaCell = range.get_Range(formulaCellAddress, formulaCellAddress);
				formulaCell.Formula = string.Format("=I{0}-G{0}", rowNumber);
				Marshal.ReleaseComObject(formulaCell);

				// get the agreement type
				formulaCellAddress = string.Format("A{0}", rowNumber);
				formulaCell = range.get_Range(formulaCellAddress, formulaCellAddress);
				agreementType = formulaCell.Value2.ToString();
				Marshal.ReleaseComObject(formulaCell);

				// projected balance
				formulaCellAddress = string.Format("M{0}", rowNumber);
				formulaCell = range.get_Range(formulaCellAddress, formulaCellAddress);
				formulaCell.Formula = agreementType == "Misc/Other" || agreementType == "Exchange" || agreementType == "Gift" || agreementType == "Trial"  ?
					string.Format("=J{0}-L{0}-K{0}", rowNumber) :  string.Format("=H{0}-L{0}-K{0}", rowNumber);
				Marshal.ReleaseComObject(formulaCell);
			}
			#endregion		
		}
		
		/// <summary>
		/// Adds formula to Projections details excel report.
		/// </summary>
		/// <param name="range">All columns within a workbook.</param>
		/// <param name="lastCell">The last cell with data.</param>
		/// <param name="personnelRecordCount">The number of records in the Personnel Budget datagrid.</param>
		private void FormatProjectionsDetails(XL.Range range, XL.Range lastCell, int personnelRecordCount)
		{
			int lastRowNumber = lastCell.Row;
			int totalPersonnelRows = personnelRecordCount + 5; // title,header,footer and two empty rows 
			int totalCashBalanceRowNumber = lastRowNumber - totalPersonnelRows - 1;
			int priorYearBalanceRowNumber = totalCashBalanceRowNumber - 1;
			int totalrevenueRowNumber = priorYearBalanceRowNumber - 1;
			int totalCostRowNumber = totalrevenueRowNumber - 1;
			int inDirectCostTotalRowNumber = totalCostRowNumber - 1;
			int directCostTotalRowNumber = inDirectCostTotalRowNumber - 1;
			int firstDataColumnNumber = 4;
			int lastDataRowNumber = directCostTotalRowNumber - 1; // for projections details
			int columnCount = 9;
			XL.Range cells = null;

			#region Projections Details Totals

			#region Category Sums
			XL.Range totalCell = null;
			XL.Range nextTotalCell = null;
			XL.Range categoryCells = range.get_Range("B13", string.Format("B{0}", lastDataRowNumber));
			
			cells = range.Cells;
			totalCell = categoryCells.Find("Total", Type.Missing, XL.XlFindLookIn.xlValues,
				XL.XlLookAt.xlWhole, Type.Missing, XL.XlSearchDirection.xlNext, true, false, Type.Missing);

			int categoryCount = categoryCells.Count;
			string totalAddress = null;
			string nextTotalAddress = null;
			string categoryRowNumber = null;
			for (int index = 0; totalCell != null && index < categoryCount; ++index)
			{
				totalAddress = totalCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
				categoryRowNumber = totalAddress.Replace("$","").Trim().Substring(1); // the first index value is a column letter
				nextTotalCell = categoryCells.FindNext(totalCell);
				
				nextTotalAddress = nextTotalCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

				// total categories cells for all the month columns in the entire row
				for (int columnNumber = firstDataColumnNumber; columnNumber <= columnCount ; ++columnNumber)
				{
					XL.Range categoryTotalCell = (XL.Range)cells[categoryRowNumber, columnNumber];
					XL.Range categorySumStartCell = null;
					XL.Range categorySumEndCell = null;

					if (totalAddress != nextTotalAddress) 
					{
						categorySumStartCell = (XL.Range)cells[totalCell.Row + 1, columnNumber];
						categorySumEndCell = (XL.Range)cells[nextTotalCell.Row - 1, columnNumber];
					} 
					else // last total row
					{
						categorySumStartCell = (XL.Range)cells[nextTotalCell.Row + 1, columnNumber];
						categorySumEndCell = (XL.Range)cells[lastDataRowNumber, columnNumber];
					}
					categoryTotalCell.Formula = string.Format("=SUM({0}:{1})",
						categorySumStartCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing),
						categorySumEndCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));

					Marshal.ReleaseComObject(categoryTotalCell);
					Marshal.ReleaseComObject(categorySumEndCell);
					Marshal.ReleaseComObject(categorySumStartCell);
				}

				Marshal.ReleaseComObject(totalCell);
				Marshal.ReleaseComObject(categoryCells);

				if (totalAddress == nextTotalAddress) // we just processed the last total row, exit the loop
				{
					Marshal.ReleaseComObject(nextTotalCell);
					break;
				}

				categoryCells = range.get_Range(string.Format("B{0}", nextTotalCell.Row - 1), string.Format("B{0}", lastDataRowNumber));
				totalCell = categoryCells.Find("Total", Type.Missing, XL.XlFindLookIn.xlValues,
					XL.XlLookAt.xlWhole, Type.Missing, XL.XlSearchDirection.xlNext, true, false, Type.Missing);
				
				Marshal.ReleaseComObject(nextTotalCell);
			}
			Marshal.ReleaseComObject(cells);

			#endregion Category Sums

			#region Column Totals
			string lastCellAdress = lastCell.get_Address(false, false, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
			int lastCellRowNumber = Convert.ToInt32(lastCellAdress.Substring(1)); // last cell address is Z..
			XL.Range formulaCell = null;

			#region Direct Cost Row
			// Budget For Current Period
			string totalCellAddress = string.Format("D{0}", directCostTotalRowNumber);
			string totalFormulaLastCellAddress = string.Format("D{0}", lastDataRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(D14:{0})/2", totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);

			// Expense To DateIn Period
			totalCellAddress = string.Format("E{0}", directCostTotalRowNumber);
			totalFormulaLastCellAddress = string.Format("E{0}", lastDataRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(E14:{0})/2", totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);

			// Encumbrances
			totalCellAddress = string.Format("G{0}", directCostTotalRowNumber);
			totalFormulaLastCellAddress = string.Format("G{0}", lastDataRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(G14:{0})/2", totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);

			// Balance
			totalCellAddress = string.Format("H{0}", directCostTotalRowNumber);
			totalFormulaLastCellAddress = string.Format("H{0}", lastDataRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(H14:{0})/2", totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);

			// "Estimated Expenses Through End of Period
			totalCellAddress = string.Format("I{0}", directCostTotalRowNumber);
			totalFormulaLastCellAddress = string.Format("I{0}", lastDataRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(I14:{0})/2", totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);
			#endregion

			#region Total Cost Row
			// Budget For Current Period
			string totalBudgetCellAddress = string.Format("D{0}", totalCostRowNumber);
			formulaCell = range.get_Range(totalBudgetCellAddress, totalBudgetCellAddress);
			formulaCell.Formula  = string.Format("=SUM(D{0},D{1})", directCostTotalRowNumber, inDirectCostTotalRowNumber);
			Marshal.ReleaseComObject(formulaCell);

			// Expense To Date In Period
			string totalExpensesCellAddress = string.Format("E{0}", totalCostRowNumber);
			formulaCell = range.get_Range(totalExpensesCellAddress, totalExpensesCellAddress);
			formulaCell.Formula  = string.Format("=SUM(E{0},E{1})", directCostTotalRowNumber, inDirectCostTotalRowNumber);
			Marshal.ReleaseComObject(formulaCell);

			// Encumbrances
			totalCellAddress = string.Format("G{0}", totalCostRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(G{0},G{1})", directCostTotalRowNumber, inDirectCostTotalRowNumber);
			Marshal.ReleaseComObject(formulaCell);

			// Balance
			totalCellAddress = string.Format("H{0}", totalCostRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(H{0},H{1})", directCostTotalRowNumber, inDirectCostTotalRowNumber);
			Marshal.ReleaseComObject(formulaCell);

			// Estimated Expenses Through End of Period
			totalCellAddress = string.Format("I{0}", totalCostRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(I{0},I{1})", directCostTotalRowNumber, inDirectCostTotalRowNumber);
			Marshal.ReleaseComObject(formulaCell);
			#endregion

			#region Cash Balance Row
			// balance available
			totalCellAddress = string.Format("E{0}", totalCashBalanceRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=E{0}+E{1}-E{2}", priorYearBalanceRowNumber, totalrevenueRowNumber, totalCostRowNumber);
			Marshal.ReleaseComObject(formulaCell);

			// balance
			totalCellAddress = string.Format("H{0}", totalCashBalanceRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=E{0}+E{1}-E{2}", priorYearBalanceRowNumber, totalrevenueRowNumber, totalCostRowNumber);
			Marshal.ReleaseComObject(formulaCell);
			#endregion

			#endregion

			#endregion

			#region Personnel Budget Totals

			if (personnelRecordCount != 0) 
			{

				#region Column Totals
				int firstDataRowNumber = lastCellRowNumber - personnelRecordCount - 2;
				// total planned salary
				totalCellAddress = string.Format("B{0}",lastCellRowNumber - 2);
				totalFormulaLastCellAddress = string.Format("B{0}", lastCellRowNumber - 3);
				formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
				formulaCell.Formula  = string.Format("=SUM(B{0}:{1})",firstDataRowNumber, totalFormulaLastCellAddress);
				Marshal.ReleaseComObject(formulaCell);

				// monthly rate
				totalCellAddress = string.Format("C{0}",lastCellRowNumber - 2);
				totalFormulaLastCellAddress = string.Format("C{0}", lastCellRowNumber - 3);
				formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
				formulaCell.Formula  = string.Format("=SUM(C{0}:{1})",firstDataRowNumber, totalFormulaLastCellAddress);
				Marshal.ReleaseComObject(formulaCell);

				// committed salary
				totalCellAddress = string.Format("E{0}",lastCellRowNumber - 2);
				totalFormulaLastCellAddress = string.Format("E{0}", lastCellRowNumber - 3);
				formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
				formulaCell.Formula  = string.Format("=SUM(E{0}:{1})",firstDataRowNumber, totalFormulaLastCellAddress);
				Marshal.ReleaseComObject(formulaCell);

				// committed fringe
				totalCellAddress = string.Format("G{0}",lastCellRowNumber - 2);
				totalFormulaLastCellAddress = string.Format("G{0}", lastCellRowNumber - 3);
				formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
				formulaCell.Formula  = string.Format("=SUM(G{0}:{1})",firstDataRowNumber, totalFormulaLastCellAddress);
				Marshal.ReleaseComObject(formulaCell);
				
				// total committed
				totalCellAddress = string.Format("H{0}",lastCellRowNumber - 2);
				totalFormulaLastCellAddress = string.Format("H{0}", lastCellRowNumber - 3);
				formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
				formulaCell.Formula  = string.Format("=SUM(H{0}:{1})",firstDataRowNumber, totalFormulaLastCellAddress);
				Marshal.ReleaseComObject(formulaCell);
				#endregion

				#region Other Formula Within rows
				lastDataRowNumber = lastCellRowNumber - 3; // for budget totals

				string formulaCellAddress;
				for (int rowNumber = firstDataRowNumber; rowNumber < lastDataRowNumber + 1; ++rowNumber)
				{
					// monthly rate
					formulaCellAddress = string.Format("C{0}", rowNumber);					
					formulaCell = range.get_Range(formulaCellAddress, formulaCellAddress);
					formulaCell.Formula = string.Format("=B{0}/12", rowNumber);
					Marshal.ReleaseComObject(formulaCell);

					// committed salary
					formulaCellAddress = string.Format("E{0}", rowNumber);
					formulaCell = range.get_Range(formulaCellAddress, formulaCellAddress);
					formulaCell.Formula = string.Format("=C{0}*D{0}", rowNumber);
					Marshal.ReleaseComObject(formulaCell);

					// committed fringe
					formulaCellAddress = string.Format("G{0}", rowNumber);
					formulaCell = range.get_Range(formulaCellAddress, formulaCellAddress);
					formulaCell.Formula = string.Format("=E{0}*F{0}", rowNumber);
					Marshal.ReleaseComObject(formulaCell);

					// total committed
					formulaCellAddress = string.Format("H{0}", rowNumber);
					formulaCell = range.get_Range(formulaCellAddress, formulaCellAddress);
					formulaCell.Formula = string.Format("=E{0}+G{0}", rowNumber);
					Marshal.ReleaseComObject(formulaCell);
				}
				#endregion		
			}

			#endregion

			#region Projections Totals
			// current balance = budget - expenses
			formulaCell = range.get_Range("A10", Type.Missing);
			formulaCell.Formula = string.Format("={0}-{1}", totalBudgetCellAddress, totalExpensesCellAddress);
			Marshal.ReleaseComObject(formulaCell);

			// estimated ending balance = total budget - total cost - total committed salary
			formulaCell = range.get_Range("C10", Type.Missing);
			formulaCell.Formula = personnelRecordCount != 0 ?
				string.Format("={0}-I{1}-H{2}", totalBudgetCellAddress, totalCostRowNumber, lastCellRowNumber - 2) : 
				string.Format("={0}-I{1}", totalBudgetCellAddress, totalCostRowNumber);
			Marshal.ReleaseComObject(formulaCell);
			#endregion
		}

		/// <summary>
		/// Adds formula to Budget excel report.
		/// </summary>
		/// <param name="range">All columns within a workbook.</param>
		/// <param name="lastCell">The last cell with data.</param>
		private void FormatBudget(XL.Range range, XL.Range lastCell)
		{
			int lastRowNumber = lastCell.Row;
			int directCostTotalRowNumber = lastRowNumber - 6;
			int inDirectCostTotalRowNumber = lastRowNumber - 5;
			int totalCostRowNumber = lastRowNumber - 4;
			int totalrevenueRowNumber = lastRowNumber - 3;
			int priorYearBalanceRowNumber = lastRowNumber - 2;
			int totalCashBalanceRowNumber = lastRowNumber - 1;
			int firstDataColumnNumber = 3;
			int lastDataRowNumber = directCostTotalRowNumber - 1;
			int columnCount = 6;
			XL.Range cells = null;

			#region Category Sums
			XL.Range totalCell = null;
			XL.Range nextTotalCell = null;
			XL.Range categoryCells = range.get_Range("B4", string.Format("B{0}", lastDataRowNumber));
			
			cells = range.Cells;
			totalCell = categoryCells.Find("Total", Type.Missing, XL.XlFindLookIn.xlValues,
				XL.XlLookAt.xlWhole, Type.Missing, XL.XlSearchDirection.xlNext, true, false, Type.Missing);

			int categoryCount = categoryCells.Count;
			string totalAddress = null;
			string nextTotalAddress = null;
			string categoryRowNumber = null;
			for (int index = 0; totalCell != null && index < categoryCount; ++index)
			{
				totalAddress = totalCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
				categoryRowNumber = totalAddress.Replace("$","").Trim().Substring(1); // the first index value is a column letter
				nextTotalCell = categoryCells.FindNext(totalCell);
				
				nextTotalAddress = nextTotalCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

				// total categories cells for all the month columns in the entire row
				for (int columnNumber = firstDataColumnNumber; columnNumber <= columnCount ; ++columnNumber)
				{
					XL.Range categoryTotalCell = (XL.Range)cells[categoryRowNumber, columnNumber];
					XL.Range categorySumStartCell = null;
					XL.Range categorySumEndCell = null;

					if (totalAddress != nextTotalAddress) 
					{
						categorySumStartCell = (XL.Range)cells[totalCell.Row + 1, columnNumber];
						categorySumEndCell = (XL.Range)cells[nextTotalCell.Row - 1, columnNumber];
					} 
					else // last total row
					{
						categorySumStartCell = (XL.Range)cells[nextTotalCell.Row + 1, columnNumber];
						categorySumEndCell = (XL.Range)cells[lastDataRowNumber, columnNumber];
					}
					categoryTotalCell.Formula = string.Format("=SUM({0}:{1})",
						categorySumStartCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing),
						categorySumEndCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));

					Marshal.ReleaseComObject(categoryTotalCell);
					Marshal.ReleaseComObject(categorySumEndCell);
					Marshal.ReleaseComObject(categorySumStartCell);
				}

				Marshal.ReleaseComObject(totalCell);
				Marshal.ReleaseComObject(categoryCells);

				if (totalAddress == nextTotalAddress) // we just processed the last total row, exit the loop
				{
					Marshal.ReleaseComObject(nextTotalCell);
					break;
				}

				categoryCells = range.get_Range(string.Format("B{0}", nextTotalCell.Row - 1), string.Format("B{0}", lastDataRowNumber));
				totalCell = categoryCells.Find("Total", Type.Missing, XL.XlFindLookIn.xlValues,
					XL.XlLookAt.xlWhole, Type.Missing, XL.XlSearchDirection.xlNext, true, false, Type.Missing);
				
				Marshal.ReleaseComObject(nextTotalCell);
			}
			Marshal.ReleaseComObject(cells);

			#endregion Category Sums

			#region Column Totals
			string lastCellAdress = lastCell.get_Address(false, false, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
			int lastCellRowNumber = Convert.ToInt32(lastCellAdress.Substring(1)); // last cell address is Z..
			XL.Range formulaCell = null;

			#region Direct Cost Row
			// budget
			string totalCellAddress = string.Format("C{0}", directCostTotalRowNumber);
			string totalFormulaLastCellAddress = string.Format("C{0}", lastDataRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(C5:{0})/2", totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);

			// expenses
			totalCellAddress = string.Format("D{0}", directCostTotalRowNumber);
			totalFormulaLastCellAddress = string.Format("D{0}", lastDataRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(D5:{0})/2", totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);

			// encumbrance
			totalCellAddress = string.Format("E{0}", directCostTotalRowNumber);
			totalFormulaLastCellAddress = string.Format("E{0}", lastDataRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(E5:{0})/2", totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);

			// balance available
			totalCellAddress = string.Format("F{0}", directCostTotalRowNumber);
			totalFormulaLastCellAddress = string.Format("F{0}", lastDataRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(F5:{0})/2", totalFormulaLastCellAddress);
			Marshal.ReleaseComObject(formulaCell);
			#endregion

			#region Total Cost Row
			// budget
			totalCellAddress = string.Format("C{0}", totalCostRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(C{0},C{1})", directCostTotalRowNumber, inDirectCostTotalRowNumber);
			Marshal.ReleaseComObject(formulaCell);

			// expenses
			totalCellAddress = string.Format("D{0}", totalCostRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(D{0},D{1})", directCostTotalRowNumber, inDirectCostTotalRowNumber);
			Marshal.ReleaseComObject(formulaCell);

			// encumbrance
			totalCellAddress = string.Format("E{0}", totalCostRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(E{0},E{1})", directCostTotalRowNumber, inDirectCostTotalRowNumber);
			Marshal.ReleaseComObject(formulaCell);

			// balance available
			totalCellAddress = string.Format("F{0}", totalCostRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=SUM(F{0},F{1})", directCostTotalRowNumber, inDirectCostTotalRowNumber);
			Marshal.ReleaseComObject(formulaCell);
			#endregion

			#region Cash Balance Row
			// expenses
			totalCellAddress = string.Format("D{0}", totalCashBalanceRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=D{0}+D{1}-D{2}", priorYearBalanceRowNumber, totalrevenueRowNumber, totalCostRowNumber);
			Marshal.ReleaseComObject(formulaCell);

			// balance available
			totalCellAddress = string.Format("F{0}", totalCashBalanceRowNumber);
			formulaCell = range.get_Range(totalCellAddress, totalCellAddress);
			formulaCell.Formula  = string.Format("=D{0}- E{1}", totalCashBalanceRowNumber, totalCostRowNumber);
			Marshal.ReleaseComObject(formulaCell);
			#endregion

			#endregion
		}
		
		/// <summary>
		/// Adds formula to Expense excel report.
		/// </summary>
		/// <param name="range">All columns within a workbook.</param>
		/// <param name="lastCell">The last cell with data.</param>
		/// <param name="columnCount">Number of months columns in the sheet.</param>
		private void FormatExpense(XL.Range range, XL.Range lastCell, int columnCount) 
		{
			XL.Range cells = null;
			XL.Range formatCell = null;
			int lastRowNumber = lastCell.Row;
			int cashBalanceTotalRowNumber = lastRowNumber - 3;
			int directCostTotalRowNumber = lastRowNumber - 19;
			int inDirectCostTotalRowNumber = lastRowNumber - 15;
			int totalCostRowNumber = lastRowNumber - 11;
			int totalrevenueRowNumber = lastRowNumber - 7;
			int totalCashBalanceRowNumber = lastRowNumber - 3;
			int firstDataRowNumber = 6;
			int firstDataColumnNumber = 3;
			int lastDataRowNumber = directCostTotalRowNumber - 5;

			// get totals rows and move them to the left by one cell so they match their respective months.
			// Crystal reports does not seem to do it right with Crosstabs.
			formatCell = range.get_Range(string.Format("A{0}", directCostTotalRowNumber), string.Format("A{0}", cashBalanceTotalRowNumber));
			formatCell.Insert(XL.XlInsertShiftDirection.xlShiftToRight, false);
			Marshal.ReleaseComObject(formatCell);

			#region Category Sums
			XL.Range totalCell = null;
			XL.Range nextTotalCell = null;
			XL.Range categoryCells = range.get_Range("B5", string.Format("B{0}", lastDataRowNumber));
			
			cells = range.Cells;
			totalCell = categoryCells.Find("Total", Type.Missing, XL.XlFindLookIn.xlValues,
					XL.XlLookAt.xlWhole, Type.Missing, XL.XlSearchDirection.xlNext, true, false, Type.Missing);

			int categoryCount = categoryCells.Count;
			string totalAddress = null;
			string nextTotalAddress = null;
			string categoryRowNumber = null;
			for (int index = 0; totalCell != null && index < categoryCount; ++index)
			{
				totalAddress = totalCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
				categoryRowNumber = totalAddress.Replace("$","").Trim().Substring(1); // the first index value is a column letter
				nextTotalCell = categoryCells.FindNext(totalCell);
				
				nextTotalAddress = nextTotalCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

				// total categories cells for all the month columns in the entire row
				for (int columnNumber = firstDataColumnNumber; columnNumber < columnCount + firstDataColumnNumber ; ++columnNumber)
				{
					XL.Range categoryTotalCell = (XL.Range)cells[categoryRowNumber, columnNumber];
					XL.Range categorySumStartCell = null;
					XL.Range categorySumEndCell = null;

					if (totalAddress != nextTotalAddress) 
					{
						categorySumStartCell = (XL.Range)cells[totalCell.Row + 1, columnNumber];
						categorySumEndCell = (XL.Range)cells[nextTotalCell.Row - 1, columnNumber];
					} 
					else // last total row
					{
						categorySumStartCell = (XL.Range)cells[nextTotalCell.Row + 1, columnNumber];
						categorySumEndCell = (XL.Range)cells[lastDataRowNumber, columnNumber];
					}
					categoryTotalCell.Formula = string.Format("=SUM({0}:{1})",
						categorySumStartCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing),
						categorySumEndCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));

					Marshal.ReleaseComObject(categoryTotalCell);
					Marshal.ReleaseComObject(categorySumEndCell);
					Marshal.ReleaseComObject(categorySumStartCell);
				}

				Marshal.ReleaseComObject(totalCell);
				Marshal.ReleaseComObject(categoryCells);

				if (totalAddress == nextTotalAddress) // we just processed the last total row, exit the loop
				{
					Marshal.ReleaseComObject(nextTotalCell);
					break;
				}

				categoryCells = range.get_Range(string.Format("B{0}", nextTotalCell.Row - 1)/*nextTotalAddress*/, string.Format("B{0}", lastDataRowNumber));
				totalCell = categoryCells.Find("Total", Type.Missing, XL.XlFindLookIn.xlValues,
					XL.XlLookAt.xlWhole, Type.Missing, XL.XlSearchDirection.xlNext, true, false, Type.Missing);
				
				Marshal.ReleaseComObject(nextTotalCell);
			}
			#endregion Category Sums

			#region All Column Totals
			string startCellAddress;
			string endCellAddress;
			object carryOverCashBalance = 0;
			object firstMonthTotalCost = 0;
			object firstMonthRevenue = 0;

			for (int columnNumber = firstDataColumnNumber; columnNumber < columnCount + firstDataColumnNumber; ++columnNumber)
			{
				formatCell = ((XL.Range)cells[firstDataRowNumber, columnNumber]);
				startCellAddress = formatCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
				Marshal.ReleaseComObject(formatCell);

				formatCell = ((XL.Range)cells[lastDataRowNumber, columnNumber]);
				endCellAddress = formatCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
				Marshal.ReleaseComObject(formatCell);
				
				// total direct cost
				formatCell = ((XL.Range)cells[directCostTotalRowNumber, columnNumber]);
				formatCell.Formula = string.Format("=SUM({0}:{1})/2", startCellAddress, endCellAddress);
				string directCostsAddress = formatCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
				Marshal.ReleaseComObject(formatCell);

				// indirect costs
				formatCell = ((XL.Range)cells[inDirectCostTotalRowNumber, columnNumber]);
				string inDirectCostsAddress = formatCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
				Marshal.ReleaseComObject(formatCell);

				// total costs
				formatCell = ((XL.Range)cells[totalCostRowNumber, columnNumber]);
				if (columnNumber == firstDataColumnNumber) 
					firstMonthTotalCost = formatCell.Value2;

				string totalCostsAddress = formatCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
				formatCell.Formula = string.Format("=SUM({0},{1})", directCostsAddress, inDirectCostsAddress);
				Marshal.ReleaseComObject(formatCell);

				// revenue
				formatCell = ((XL.Range)cells[totalrevenueRowNumber, columnNumber]);
				if (columnNumber == firstDataColumnNumber) 
					firstMonthRevenue = formatCell.Value2;

				string totalRevenueAddress = formatCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
				Marshal.ReleaseComObject(formatCell);

				// cash balance cell
				formatCell = ((XL.Range)cells[totalCashBalanceRowNumber, columnNumber]);

				// get the beginning balance
				if (columnNumber == firstDataColumnNumber)
					carryOverCashBalance = Convert.ToDecimal(formatCell.Value2) - Convert.ToDecimal(firstMonthRevenue) + Convert.ToDecimal(firstMonthTotalCost);

				string totalCashBalanceAddress = formatCell.get_Address(Type.Missing, Type.Missing, XL.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
				formatCell.Formula = string.Format("={0}-{1}+{2}", totalRevenueAddress, totalCostsAddress, carryOverCashBalance);
				carryOverCashBalance = formatCell.Value2;
				Marshal.ReleaseComObject(formatCell);
			}
			Marshal.ReleaseComObject(cells);

			#endregion All Column Totals
		}

		/// <summary>
		/// Adds additional formatting to Personnel details excel report.
		/// </summary>
		/// <param name="range">All columns within a workbook.</param>
		/// <param name="lastCell">The last cell with data.</param>
		private void FormatPersonnelDetails(XL.Range range, XL.Range lastCell) 
		{
		}
		#endregion
	}
	#endregion
}

// --- EOF ---
